package cn.ztydata.teachingsystem.heaven.dao;

import cn.ztydata.teachingsystem.common.Page;
import cn.ztydata.teachingsystem.heaven.common.WebContext;
import cn.ztydata.teachingsystem.heaven.cons.Roles;
import cn.ztydata.teachingsystem.heaven.entity.Class;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * ClassGroupDao
 *
 * @author lyj
 * @since 2015/5/5
 */
@Repository
public class ClassGroupDao extends BaseDao {
    @Autowired
    private WebContext webContext;


    /**
     * 获取当前班级群组列表
     *
     * @param pageNum    当前页码
     * @param pageSize   每页条数
     * @param teacherNum 教师工号
     * @return page
     * @author lyj
     * @since 2015-5-5
     */
    public Page<Class> getAllClasses(final int pageNum, final int pageSize, final String teacherNum) {
        StringBuilder sql = new StringBuilder("SELECT cl.id, cl.sNumber, cl.sName AS clName, spe.sName AS speName, te.sName AS teName FROM t_class cl " +
                "LEFT JOIN t_specialty spe ON spe.sNumber = cl.sSpecNum " +
                "LEFT JOIN t_teacher te ON cl.sTeacherNum = te.sNumber " +
                "WHERE cl.sTeacherNum = :teacherNum");
        StringBuilder sqlCount = new StringBuilder("SELECT COUNT(cl.id) FROM t_class cl " +
                "LEFT JOIN t_specialty spe ON spe.sNumber = cl.sSpecNum " +
                "LEFT JOIN t_teacher te ON cl.sTeacherNum = te.sNumber " +
                "WHERE cl.sTeacherNum = :teacherNum");

        MapSqlParameterSource param = new MapSqlParameterSource().addValue("teacherNum", teacherNum);

        //获取总条数
        int total = namedParameterJdbcTemplate.queryForObject(sqlCount.toString(), param, Integer.class);

        if (total == 0) {
            log.debug("total == 0,new Page");
            return new Page<>();
        }

        //追加limit限制
        sql.append(getLimitStr(pageNum, pageSize));

        List<Class> classList = namedParameterJdbcTemplate.query(sql.toString(), param, new RowMapper<Class>() {
            @Override
            public Class mapRow(ResultSet resultSet, int i) throws SQLException {
                Class classe = new Class();

                classe.setId(resultSet.getLong("id"));
                classe.setNumber(resultSet.getString("sNumber"));
                classe.setName(resultSet.getString("clName"));
                classe.setSpecName(resultSet.getString("speName"));

                return classe;

            }
        });

        log.debug("2.获取当前班级群组列表");
        return new Page<>(total, pageSize, pageNum, classList);
    }


    /**
     * 获取历史班级群组列表
     *
     * @param pageNum    当前页码
     * @param pageSize   每页条数
     * @param teacherNum 教师工号
     * @return page
     * @author lyj
     * @since 2015-5-5
     */
    public Page<Class> getHistoryClasses(final int pageNum, final int pageSize, final String search, final String teacherNum) {
        StringBuilder sql = new StringBuilder("SELECT cl.id, cl.sNumber, cl.sName AS clName, spe.sName AS speName, te.sName AS teName FROM t_hclass cl " +
                "LEFT JOIN t_specialty spe ON spe.sNumber = cl.sSpecNum " +
                "LEFT JOIN t_teacher te ON cl.sTeacherNum = te.sNumber " +
                "WHERE cl.sTeacherNum = :teacherNum");
        StringBuilder sqlCount = new StringBuilder("SELECT COUNT(cl.id) FROM t_hclass cl " +
                "LEFT JOIN t_specialty spe ON spe.sNumber = cl.sSpecNum " +
                "LEFT JOIN t_teacher te ON cl.sTeacherNum = te.sNumber " +
                "WHERE cl.sTeacherNum = :teacherNum");

        StringBuilder where = new StringBuilder(" AND 1 = 1");
        MapSqlParameterSource param = new MapSqlParameterSource().addValue("teacherNum", teacherNum);

        //过滤关键字
        if (!search.isEmpty()) {
            log.debug("!search.isEmpty(),过滤关键字");
            where.append(" AND (UPPER(cl.sName) LIKE :search OR UPPER(cl.sNumber) LIKE :search)");
            param.addValue("search", "%" + search.toUpperCase() + "%");
        }

        //获取总条数
        int total = namedParameterJdbcTemplate.queryForObject(sqlCount.append(where).toString(), param, Integer.class);

        if (total == 0) {
            log.debug("2.total == 0,new Page");
            return new Page<>();
        }

        //追加limit限制
        where.append(getLimitStr(pageNum, pageSize));

        List<Class> classList = namedParameterJdbcTemplate.query(sql.append(where).toString(), param, new RowMapper<Class>() {
            @Override
            public Class mapRow(ResultSet resultSet, int i) throws SQLException {
                Class classe = new Class();

                classe.setId(resultSet.getLong("id"));
                classe.setNumber(resultSet.getString("sNumber"));
                classe.setName(resultSet.getString("clName"));
                classe.setSpecName(resultSet.getString("speName"));

                return classe;

            }
        });

        log.debug("2.获取历史班级群组列表");
        return new Page<>(total, pageSize, pageNum, classList);
    }


    /**
     * 获取当前班级群组明细头部
     *
     * @param classId 班级ID
     * @return Class
     * @author lyj
     * @since 2015-5-5
     */
    public Class getClassTop(final long classId) {
        StringBuilder sql = new StringBuilder("SELECT cl.sName AS clName, te.sName AS teName, te.sPicture FROM t_class cl " +
                "LEFT JOIN t_teacher te ON cl.sTeacherNum = te.sNumber WHERE cl.id = :classId");

        try {
            log.debug("获取当前班级群组明细头部");
            return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("classId", classId), new RowMapper<Class>() {
                @Override
                public Class mapRow(ResultSet resultSet, int i) throws SQLException {
                    Class classe = new Class();

                    classe.setName(resultSet.getString("clName"));
                    classe.setTeacherName(resultSet.getString("teName"));
                    classe.setTeacherPhoto(resultSet.getString("sPicture"));

                    return classe;
                }

            });
        } catch (EmptyResultDataAccessException ex) {
            log.debug("获取班级明细头部信息失败！EmptyResultDataAccessException");
            log.warn("获取班级明细头部信息失败！");
            return null;
        }
    }

    /**
     * 获取历史班级群组明细头部
     *
     * @param classId 班级ID
     * @return Class
     * @author lyj
     * @since 2015-5-5
     */
    public Class getHistoryClassTop(final long classId) {
        StringBuilder sql = new StringBuilder("SELECT cl.sName AS clName, te.sName AS teName, te.sPicture FROM t_hclass cl " +
                "LEFT JOIN t_teacher te ON cl.sTeacherNum = te.sNumber WHERE cl.id = :classId");

        try {
            log.debug("获取历史班级群组明细头部");
            return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("classId", classId), new RowMapper<Class>() {
                @Override
                public Class mapRow(ResultSet resultSet, int i) throws SQLException {
                    Class classe = new Class();

                    classe.setName(resultSet.getString("clName"));
                    classe.setTeacherName(resultSet.getString("teName"));
                    classe.setTeacherPhoto(resultSet.getString("sPicture"));

                    return classe;
                }

            });
        } catch (EmptyResultDataAccessException ex) {
            log.debug("获取历史班级明细头部信息失败！EmptyResultDataAccessException");
            log.warn("获取历史班级明细头部信息失败！");
            return null;
        }
    }


    /**
     * 根据班级ID获取班主任工号
     *
     * @param id
     * @return String
     * @author lyj
     * @since 2015-5-7
     */
    public String getTeacherCodeById(final long id) {
        StringBuilder sql = new StringBuilder("SELECT sTeacherNum FROM t_class WHERE id = :id");

        try {
            return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("id", id), new RowMapper<String>() {
                @Override
                public String mapRow(ResultSet resultSet, int i) throws SQLException {
                    return resultSet.getString("sTeacherNum");
                }
            });
        } catch (EmptyResultDataAccessException ex) {
            log.warn("获取班主任工号失败！");
            return null;
        }
    }

    /**
     * 根据number、groupId判断登录用户是否属于该班级群组
     *
     * @param groupId 群组ID
     * @param number 工号/学号
     * @return int
     * @author lyj
     * @since 2015-5-13
     */
    public int checkGroupIdIsNum(final long groupId, final String number) {
        StringBuilder sql = new StringBuilder();
        if (webContext.getRole() == Roles.STUDENT) {
            sql.append(" SELECT" +
                    " (SELECT COUNT(c.id) FROM t_class c JOIN t_student s ON c.sNumber = s.sClassNum WHERE s.sNumber = :number AND c.id = :id) countClassGroup," +
                    " COUNT(hc.id) AS countHClassGroup FROM t_hclass hc JOIN t_student s ON hc.sNumber = s.sClassNum WHERE s.sNumber = :number AND hc.id = :id");
        } else {
            sql.append("SELECT" +
                    "(SELECT COUNT(c.id) FROM t_class c WHERE c.id = :id AND c.sTeacherNum = :number) countClassGroup," +
                    " COUNT(hc.id) AS countHClassGroup FROM t_hclass hc WHERE hc.id = :id AND hc.sTeacherNum = :number");
        }


        Map<String, Object> map = namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource()
                .addValue("id", groupId)
                .addValue("number", number), new RowMapper<Map<String, Object>>() {
            @Override
            public Map<String, Object> mapRow(ResultSet resultSet, int i) throws SQLException {
                Map<String, Object> reMap = new HashMap<String, Object>();

                reMap.put("countClassGroup", resultSet.getInt("countClassGroup"));
                reMap.put("countHClassGroup", resultSet.getInt("countHClassGroup"));

                return reMap;
            }
        });

        return (int) map.get("countClassGroup") + (int) map.get("countHClassGroup");

    }
}
